# -*- codeing = utf-8 -*-
# @Time: 2022/4/28 19:12
# @Author: Foxhuty
# @File: HSC_02.py
# @Software: PyCharm
# @Based on python 3.10
import pandas as pd
import numpy as np
import os


class RankDistribution(object):

    def __init__(self, file_path, file_path_1):
        self.file_path = file_path
        self.file_path_1 = file_path_1
        # file_path = r'D:\work documents\高2021级\高一下半期考试\高2021级高一（下）半期考试.xlsx'
        # file_path_1 = r'D:\work documents\高2021级\高一下半期考试\高2021级文理分班信息表(对比基准）.xlsx'
        self.df_arts = pd.read_excel(file_path, sheet_name='文科', index_col='序号')
        self.df_science = pd.read_excel(file_path, sheet_name='理科', index_col='序号')
        self.df_arts_1 = pd.read_excel(file_path_1, sheet_name='文科', index_col='序号')
        self.df_science_1 = pd.read_excel(file_path_1, sheet_name='理科', index_col='序号')

    def rank_by_class_loop(self, n, arts_data=None, science_data=None):
        arts_subjects = ['语文', '数学', '英语', '政治', '历史', '地理', '总分']
        science_subjects = ['语文', '数学', '英语', '物理', '化学', '生物', '总分']
        arts_subjects_range = []
        science_subjects_range = []
        for subject in arts_subjects:
            subject_range = self.rank_by_subject(arts_data, subject, n)
            arts_subjects_range.append(subject_range)
        arts_range = pd.concat(arts_subjects_range, axis=1)
        arts_range.index.rename('班级', inplace=True)
        arts_range.sort_index(inplace=True, ascending=True)
        arts_range.fillna(0, inplace=True)
        print(arts_range)
        for subject in science_subjects:
            subject_range = self.rank_by_subject(science_data, subject, n)
            science_subjects_range.append(subject_range)
        science_range = pd.concat(science_subjects_range, axis=1)
        science_range.index.rename('班级', inplace=True)
        science_range.sort_index(inplace=True, ascending=True)
        science_range.fillna(0, inplace=True)

        # print(science_range)

        return arts_range, science_range

    def contrast_range(self, n):
        arts_range, science_range = self.rank_by_class_loop(n, arts_data=self.df_arts, science_data=self.df_science)

        arts_contrast, science_contrast = self.rank_by_class_loop(n, arts_data=self.df_arts_1,
                                                                  science_data=self.df_science_1)

        arts_contrast_result = arts_range - arts_contrast
        art_result = pd.concat([arts_range, arts_contrast, arts_contrast_result], keys=['分段人数', '对比人数', '对比变化'])
        science_contrast_result = science_range - science_contrast
        science_result = pd.concat([science_range, science_contrast, science_contrast_result],
                                   keys=['分段人数', '对比人数', '对比变化'])
        print(art_result)

        return art_result, science_result

    def main_rank_range(self):
        arts_rank_10, science_rank_10 = self.rank_by_class_loop(10, arts_data=self.df_arts,
                                                                science_data=self.df_science)
        arts_rank_30, science_rank_30 = self.rank_by_class_loop(30, arts_data=self.df_arts,
                                                                science_data=self.df_science)
        arts_rank_50, science_rank_50 = self.rank_by_class_loop(50, arts_data=self.df_arts,
                                                                science_data=self.df_science)
        arts_rank_100, science_rank_100 = self.rank_by_class_loop(100, arts_data=self.df_arts,
                                                                  science_data=self.df_science)

        arts_rank_10 = self.write_open(arts_rank_10)
        science_rank_10 = self.write_open(science_rank_10)
        arts_rank_30 = self.write_open(arts_rank_30)
        science_rank_30 = self.write_open(science_rank_30)
        arts_rank_50 = self.write_open(arts_rank_50)
        science_rank_50 = self.write_open(science_rank_50)
        arts_rank_100 = self.write_open(arts_rank_100)
        science_rank_100 = self.write_open(science_rank_100)

        rank_range_arts = pd.concat([arts_rank_10, arts_rank_30, arts_rank_50, arts_rank_100])
        rank_range_science = pd.concat([science_rank_10, science_rank_30, science_rank_50, science_rank_100])
        rank_range_arts.fillna(0, inplace=True)
        rank_range_science.fillna(0, inplace=True)
        with pd.ExcelWriter(r'D:\成绩统计结果\名次段分布.xlsx') as writer:
            rank_range_arts.to_excel(writer, sheet_name='文科名次分布', index=False)
            rank_range_science.to_excel(writer, sheet_name='理科名次分布', index=False)

        print(f'successfully done')

    def run_rank_change(self):
        arts_minus_10, science_minus_10 = self.contrast_range(10)
        arts_minus_30, science_minus_30 = self.contrast_range(30)
        arts_minus_50, science_minus_50 = self.contrast_range(50)
        arts_minus_100, science_minus_100 = self.contrast_range(100)
        arts_minus_10 = self.write_open(arts_minus_10)
        arts_minus_30 = self.write_open(arts_minus_30)
        arts_minus_50 = self.write_open(arts_minus_50)
        arts_minus_100 = self.write_open(arts_minus_100)

        science_minus_10 = self.write_open(science_minus_10)
        science_minus_30 = self.write_open(science_minus_30)
        science_minus_50 = self.write_open(science_minus_50)
        science_minus_100 = self.write_open(science_minus_100)
        arts = pd.concat([arts_minus_10, arts_minus_30, arts_minus_50, arts_minus_100], ignore_index=True)
        science = pd.concat([science_minus_10, science_minus_30, science_minus_50, science_minus_100],
                            ignore_index=True)
        with pd.ExcelWriter(r'D:\成绩统计结果\名次段对比变化.xlsx') as writer:
            arts.to_excel(writer, sheet_name='文科对比', index=False)
            science.to_excel(writer, sheet_name='理科对比', index=False)
        print('successfully done')

    def main(self):
        self.run_rank_change()
        self.main_rank_range()

    @staticmethod
    def write_open(df_data):
        """
        used to concat different dataframes.
        :param df_data:
        :return:
        """
        df_data.to_excel('temp_data.xlsx')
        df_new = pd.read_excel('temp_data.xlsx', header=None)
        os.remove('temp_data.xlsx')
        return df_new

    @staticmethod
    def rank_by_subject(data, subject, n):
        data['排名'] = data[subject].rank(method='min', ascending=False)
        df_rank = data[data['排名'] <= n]
        df_rank_num = df_rank['班级'].value_counts()
        df_rank_num.name = f'{subject}前{n}名'
        return df_rank_num


if __name__ == '__main__':
    file_path = r'D:\年级管理数据\高2021级\高一下\高一下期末考试成绩分析\十一中\高一年级期末考试总分成绩.xlsx'
    file_path_1 = r'D:\work documents\高2021级\高一下半期考试\高2021级文理分班信息表(对比基准）.xlsx'
    rank_contrast = RankDistribution(file_path, file_path_1)
    rank_contrast.run_rank_change()
